


ALTER   proc sp_all_sales_orders_report

	@From_Date varchar(20),
	@To_Date varchar(20),
	@Customer_ID int,
	@Product_ID int,
	@Region varchar(30),
	@RepOffice varchar(30),
	@EndUser varchar (30),
	@SalesPerson varchar(30)



as
begin

	declare @results Table(SO bigint, ProductName Varchar(30), CustomerName Varchar(65), ItemNo Varchar(10), Status Varchar(30), BookedDate datetime, 
	ReqDocDate datetime, SchdDate datetime, RevSchdDate datetime, Qty bigint, UNitcost float, ExtPrice float)
	
	declare @so bigint
	declare @productName Varchar(30), @customerName Varchar(65), @itemNo Varchar(10), @status Varchar(30)
	declare @bookedDate datetime, @reqDocDate datetime, @schdDate datetime, @revSchdDate datetime
	declare @qty bigint
	declare @unitcost float, @extPrice float

	declare cocursor cursor for
	SELECT
		Sales_Order.SalesOrder_No, 
		Product.Part_Number,
		Customer.C_Name, 
		Customer_Open_Order_LineItem.COOL_Line_Item_No,
		Sales_Order.SO_Status,
		Customer_Open_Order_LineItem.COOL_Booking_Date,
		Customer_Open_Order_LineItem.COOL_Requested_Date,
		Customer_Open_Order_LineItem.COOL_Schedule_Date,
		Customer_Open_Order_LineItem.COOL_Revised_Date,
		Customer_Open_Order_LineItem.COOL_Qty, 
		Customer_Open_Order_LineItem.COOL_Unit_Cost, 
		Customer_Open_Order_LineItem.COOL_Amount
	   
		FROM
		    (( Sales_Order INNER JOIN Customer_Open_Order_LineItem ON
		        	Sales_Order.SalesOrder_ID = Customer_Open_Order_LineItem.SalesOrder_ID)
		     		INNER JOIN  Customer ON
		        	Sales_Order.Customer_ID = Customer.Customer_ID)
		    		INNER JOIN Product ON
		        	Customer_Open_Order_LineItem.Product_ID = Product.Product_ID
	open cocursor
	fetch next from cocursor into
	@so,@productName, @customerName, @itemNo, @status, @bookedDate, @reqDocDate, @schdDate, @revSchdDate, @qty, 
	@unitcost, @extPrice 
	while @@fetch_status = 0
	begin
		insert into @results values(@so,@productName, @customerName, @itemNo, @status, @bookedDate, @reqDocDate, @schdDate, @revSchdDate, @qty, 
		@unitcost, @extPrice)
		fetch next from cocursor into
		@so,@productName, @customerName, @itemNo, @status, @bookedDate, @reqDocDate, @schdDate, @revSchdDate, @qty, 
		@unitcost, @extPrice 
	end
	close cocursor
	deallocate cocursor

	declare cccursor cursor for
	SELECT
		Sales_Order.SalesOrder_No, 
		Product.Part_Number,
		Customer.C_Name, 
		Customer_Cancel_Order_LineItem.CCOL_Line_Item_No,
		Sales_Order.SO_Status,
		Customer_Cancel_Order_LineItem.CCOL_Booking_Date,
		Customer_Cancel_Order_LineItem.CCOL_Requested_Date,
		Customer_Cancel_Order_LineItem.CCOL_Schedule_Date,
		Customer_Cancel_Order_LineItem.CCOL_Revised_Date,
		Customer_Cancel_Order_LineItem.CCOL_Qty, 
		Customer_Cancel_Order_LineItem.CCOL_Unit_Cost, 
		Customer_Cancel_Order_LineItem.CCOL_Amount
	   
		FROM
		     (( Sales_Order INNER JOIN Customer_Cancel_Order_LineItem ON
		        Sales_Order."SalesOrder_ID" = Customer_Cancel_Order_LineItem.SalesOrder_ID)
		     INNER JOIN  Customer ON
		        Sales_Order.Customer_ID = Customer.Customer_ID)
		     INNER JOIN  Product ON
		        Customer_Cancel_Order_LineItem.Product_ID = Product.Product_ID
	open cccursor
	fetch next from cccursor into
	@so,@productName, @customerName, @itemNo, @status, @bookedDate, @reqDocDate, @schdDate, @revSchdDate, @qty, 
	@unitcost, @extPrice 
	while @@fetch_status = 0
	begin
		insert into @results values(@so,@productName, @customerName, @itemNo, 'CANCELLED', @bookedDate, @reqDocDate, @schdDate, @revSchdDate, @qty, 
		@unitcost, @extPrice)
		fetch next from cccursor into
		@so,@productName, @customerName, @itemNo, @status, @bookedDate, @reqDocDate, @schdDate, @revSchdDate, @qty, 
		@unitcost, @extPrice 
	end
	close cccursor
	deallocate cccursor


end


if (@From_Date ='0' and @To_Date = '0' and @Customer_ID = 0 and @Product_ID =0 and
@Region ='0' and @RepOffice ='0' and @EndUser ='0' and @SalesPerson ='0')
	begin
		select * from @results order by SO
	end





